# -*- coding:utf-8 -*-
import traceback
from openpyxl import load_workbook
import time
import subprocess
import paramiko
import raid_cmd
from file_handle import copy_and_rename_file
from log import logger
from redfish import RedFishService
import datetime

# 带外机器ip、账号、密码
host_ip = input('请输入带外ip：')
username = input('请输入带外账号：')
password = input('请输入带外密码：')

# 是否测试带内检测项
check_pxe = input('是否测试带内检测项：')

# 带内机器ip、账号、密码
pxe_host_ip = input('请输入带内ip：')
pxe_username = 'root'
pxe_password = 'cds-china'


# 调用函数复制文件并重命名
copy_and_rename_file("input.xlsx", "destination_file.xlsx")

# 带外ipmi指令
ipmi_read_row = ['D23', 'D24', 'D26', 'D29', 'D30', 'D41', 'D44', 'D46']
# ipmi_read_row = []

# 带外redfish指令
redfish_read_row = ['D28', 'D32', 'D33', 'D34', 'D35', 'D36']
# redfish_read_row = []

# raid配置命令
raid_read_row = ['D54', 'D55', 'D56', 'D57', 'D58', 'D59']
# raid_read_row = ['D54', 'D55']

# 带内基本信息指令
ssh_read_row = ['D63', 'D64', 'D65', 'D66']
# ssh_read_row = []

if check_pxe.lower() == 'n':
    raid_read_row, ssh_read_row = [], []

# 打开现有的 Excel 文件进行读取
workbook = load_workbook('testReport.xlsx', read_only=False)
worksheet = workbook.active  # 默认读取第一个工作表

logger.info('测试开始时间：%s' % str(datetime.datetime.now()))

# 读取单元格的值
for row in ipmi_read_row:
    values = worksheet[row].value.split('\n')
    input_save, output_save, status_code = '', '', '通过'
    for value in values:
        # 执行命令ipmi或者raid或者基础命令
        if 'ipmitool' in value:
            value = value.format(host_ip=host_ip, username=username, password=password)
            logger.info('执行命令： %s' % value)
            input_save += value + '\n' + '\n'
            try:
                output = subprocess.run(value.split(' '), stdout=subprocess.PIPE, stderr=subprocess.PIPE)

                if output.returncode != 0:
                    status_code = '不通过'
                    logger.info('命令执行报错： %s' % output.stderr.decode())
                    output_save += output.stderr.decode() + '\n'
                # 如果正确  则保存输出
                else:
                    logger.info('命令输出： %s' % output.stdout.decode())
                    # 拼接单元格的值
                    output_save += output.stdout.decode() + '\n'
            except Exception as e:
                logger.info('命令执行报错e： %s' % e)
    row_num = row.split('D')[-1]
    # 修改单元格的命令流
    worksheet[row] = input_save
    # 修改单元格的返回值
    # D20-D23合并了单元格 实际为D20
    if row_num == '23':
        row_num = '20'
    worksheet['F' + row_num] = status_code
    worksheet['G' + row_num] = output_save
    # 保存对 Excel 文件的修改
    workbook.save('testReport.xlsx')
    time.sleep(1)

for redfish_row in redfish_read_row:
    url = worksheet[redfish_row].value.split('\n')
    input_save, output_save, status_code = '', '', '通过'
    # 获取members成员链接
    members_rsp, new_url = RedFishService({'ip': host_ip, 'username': username, 'password': password}).http('GET', url[0], {})
    if members_rsp.status_code >= 300:
        input_save, output_save, status_code = new_url, members_rsp.content, '不通过'
    else:
        input_save, output_save = new_url, members_rsp.content
        members_url = members_rsp.json().get('Members')
        if members_url or 'Bios' in url:
            if members_url:
                members_url = members_url[0].get('@odata.id')
                rsp, new_url = RedFishService({'ip': host_ip, 'username': username, 'password': password}).http('GET', 'https://' + host_ip + members_url, {})
                if rsp.status_code >= 300:
                    status_code = '不通过'
                input_save, output_save = new_url, rsp.content
            else:
                input_save, output_save = url, members_rsp.content
    row_num = redfish_row.split('D')[-1]
    # 修改单元格的命令流
    worksheet[redfish_row] = input_save
    worksheet['F' + row_num] = status_code
    # 修改单元格的返回值
    worksheet['G' + row_num] = output_save
    # 保存对 Excel 文件的修改
    workbook.save('testReport.xlsx')
    time.sleep(1)

# 初始化ssh连接
# 创建 SSH 客户端
if check_pxe.lower() == 'y':
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    try:
        # 连接到远程主机
        client.connect(hostname=pxe_host_ip, port=22, username=pxe_username, password=pxe_password)
        try:
            # 获取eid和slot信息
            eid = raid_cmd.Megacli(client).get_adp_enclosure(0)
            slot_list = raid_cmd.Megacli(client).get_slot_num(0)
            del_ld_res, status_code = '', '通过'
        # 处理raid逻辑
            for raid_row in raid_read_row:
                output_save = ''
                if '54' in raid_row:
                    adp_info = raid_cmd.Megacli(client).get_adp_info()
                    raid_num = raid_cmd.Megacli(client).get_ld_count(0)
                    ld_pd_info = raid_cmd.Megacli(client).get_ld_brief_info(0)
                    info = raid_cmd.StorcliX86(client).get_info(0)
                    output_save += 'eid是： %s' % eid + '\n'
                    output_save += 'slot_list是： %s' % slot_list + '\n'
                    output_save += 'raid控制器总数是：' + str(adp_info) + '\n'
                    output_save += 'raid卡总数是：' + str(raid_num) + '\n'
                    output_save += '逻辑盘Target Id是：' + str(ld_pd_info) + '\n'
                    output_save += 'storcli获取raid信息是：' + str(info) + '\n'
                    with open(pxe_host_ip + '.txt', 'w') as f:
                        f.write('eid是： %s' % eid + '\n')
                        f.write('slot_list是： %s' % slot_list + '\n')
                        f.write('raid控制器总数是：' + str(adp_info) + '\n')
                        f.write('raid卡总数是：' + str(raid_num) + '\n')
                        f.write('逻辑盘Target Id是：' + str(ld_pd_info) + '\n')
                        f.write('storcli获取raid信息是：' + str(info) + '\n')
                if '55' in raid_row:
                    # raid全清
                    del_all_res = raid_cmd.Megacli(client).del_all(0)
                    set_jbod_res = raid_cmd.Megacli(client).set_jbod(0)
                    set_disk_jbod_res = raid_cmd.Megacli(client).set_disk_jbod(0, eid, slot_list[0])
                    del_disk_jbod_res = raid_cmd.Megacli(client).del_disk_jbod(0, eid, slot_list[0])
                    del_jbod_res = raid_cmd.Megacli(client).del_jbod()
                    output_save += 'raid全清：%s' % del_all_res + '\n'
                    output_save += '设置直通模式：%s' % set_jbod_res + '\n'
                    output_save += '设置单个磁盘直通模式：%s' % set_disk_jbod_res + '\n'
                    output_save += '清理单个磁盘直通模式：%s' % del_disk_jbod_res + '\n'
                    output_save += '取消直通模式：%s' % del_jbod_res + '\n'
                    with open(pxe_host_ip + '.txt', 'a') as f:
                        f.write('raid全清：%s' % del_all_res + '\n')
                        f.write('设置直通模式：%s' % set_jbod_res + '\n')
                        f.write('设置单个磁盘直通模式：%s' % set_disk_jbod_res + '\n')
                        f.write('清理单个磁盘直通模式：%s' % del_disk_jbod_res + '\n')
                        f.write('取消直通模式：%s' % del_jbod_res + '\n')
                # 做raid0
                if '56' in raid_row and slot_list:
                    create_res = raid_cmd.Megacli(client).create_raid(0, '%s:%s' % (eid, slot_list[0]), 0)
                    ld_list = raid_cmd.Megacli(client).get_ld_brief_info(0)
                    for ld in ld_list:
                        del_ld_res = raid_cmd.Megacli(client).del_ld(ld['Target Id'], 0)
                    output_save += '设置单盘raid：%s' % create_res + '\n'
                    output_save += '清理单盘raid：%s' % del_ld_res + '\n'
                    with open(pxe_host_ip + '.txt', 'a') as f:
                        f.write('设置单盘raid：%s' % create_res + '\n')
                        f.write('清理单盘raid：%s' % del_ld_res + '\n')

                # 做raid1
                if '57' in raid_row and len(slot_list) > 1:
                    create_res = raid_cmd.Megacli(client).create_raid(1, '%s:%s,%s:%s' % (eid, slot_list[0], eid, slot_list[1]), 0)
                    ld_list = raid_cmd.Megacli(client).get_ld_brief_info(0)
                    for ld in ld_list:
                        del_ld_res = raid_cmd.Megacli(client).del_ld(ld['Target Id'], 0)
                    output_save += '设置单盘raid：%s' % create_res + '\n'
                    output_save += '清理单盘raid：%s' % del_ld_res + '\n'
                    with open(pxe_host_ip + '.txt', 'a') as f:
                        f.write('设置单盘raid：%s' % create_res + '\n')
                        f.write('清理单盘raid：%s' % del_ld_res + '\n')
                # 做raid5
                if '58' in raid_row and len(slot_list) > 2:
                    create_res = raid_cmd.Megacli(client).create_raid(5, '%s:%s,%s:%s,%s:%s' % (eid, slot_list[0], eid, slot_list[1], eid, slot_list[2]), 0)
                    ld_list = raid_cmd.Megacli(client).get_ld_brief_info(0)
                    for ld in ld_list:
                        del_ld_res = raid_cmd.Megacli(client).del_ld(ld['Target Id'], 0)
                    output_save += '设置单盘raid：%s' % create_res + '\n'
                    output_save += '清理单盘raid：%s' % del_ld_res + '\n'
                    with open(pxe_host_ip + '.txt', 'a') as f:
                        f.write('设置单盘raid：%s' % create_res + '\n')
                        f.write('清理单盘raid：%s' % del_ld_res + '\n')
                # 做raid10
                if '59' in raid_row and len(slot_list) > 3:
                    create_res = raid_cmd.Megacli(client).create_raid(10, '%s:%s,%s:%s,%s:%s,%s:%s' % (eid, slot_list[0], eid, slot_list[1], eid, slot_list[2], eid, slot_list[3]), 0)
                    ld_list = raid_cmd.Megacli(client).get_ld_brief_info(0)
                    for ld in ld_list:
                        del_ld_res = raid_cmd.Megacli(client).del_ld(ld['Target Id'], 0)
                    output_save += '设置单盘raid：%s' % create_res + '\n'
                    output_save += '清理单盘raid：%s' % del_ld_res + '\n'
                    with open(pxe_host_ip + '.txt', 'a') as f:
                        f.write('设置单盘raid：%s' % create_res + '\n')
                        f.write('清理单盘raid：%s' % del_ld_res + '\n')
                if '异常' in output_save:
                    status_code = '不通过'
                # 存储结果
                row_num = raid_row.split('D')[-1]
                worksheet['F' + row_num] = status_code
                # 修改单元格的返回值
                worksheet['G' + row_num] = output_save
                # 保存对 Excel 文件的修改
                workbook.save('testReport.xlsx')
                time.sleep(1)
        except Exception as e:
            logger.info('raid error：%s' % e)

        for ssh_row in ssh_read_row:
            values = worksheet[ssh_row].value.split('\n')
            output_save = ''
            # 执行同一单元格的多条命令
            for value in values:
                if value:
                    logger.info('执行命令： %s' % value)
                    # 执行远程命令
                    stdin, stdout, stderr = client.exec_command(value)
                    # 输出命令执行结果
                    for err_line in stderr:
                        if err_line:
                            logger.info('命令执行报错： %s' % err_line)
                            output_save += err_line + '\n'
                    for out_line in stdout:
                        logger.info('命令输出： %s' % out_line)
                        output_save += out_line + '\n'

            # 存储结果
            row_num = ssh_row.split('D')[-1]
            # 修改单元格的返回值
            worksheet['G' + row_num] = output_save
            # 保存对 Excel 文件的修改
            workbook.save('testReport.xlsx')
            time.sleep(1)
        # 关闭连接
        client.close()

    except Exception as e:
        logger.info('error: %s' % e, exc_info=True)
        client.close()
